package com.bj58.mis.apm.server.logreceiver;

/**
 * @author: zhangfx
 * @create: 2018-07-17 11:43
 **/
public class SqlFormat {
    public static void main(String[] args){
        String sql = "select t.*, c.name cubeName\n" +
                "        from ( select a.cubedesc_id, a.totalRowkey, ifnull(u.usedRowkey,0) usedRowkey, ifnull(u.usedRowkey/a.totalRowkey, 0) usageRate from(\n" +
                "                select rk.cubedesc_id, count(*) totalRowkey\n" +
                "                from kylin_cube c\n" +
                "                join kylin_cubedesc_rowkey rk on rk.cubedesc_id = c.cubedesc_id\n" +
                "                where 1=1 \n" +
                "                group by rk.cubedesc_id\n" +
                "            ) a\n" +
                "            left join (\n" +
                "                select rk.cubedesc_id, count(*) usedRowkey\n" +
                "                from kylin_cube c\n" +
                "                join kylin_cubedesc_rowkey rk on rk.cubedesc_id = c.cubedesc_id\n" +
                "                where exists (\n" +
                "                        select 1\n" +
                "                        from kylin_query_log q\n" +
                "                        where q.cube_id = c.id\n" +
                "                            and (\n" +
                "                                FIND_IN_SET(CONCAT(rk.table_name,'.',rk.column_name),q.group_list) or FIND_IN_SET(CONCAT(rk.table_name,'.',rk.column_name),q.filter_list)\n" +
                "                            )\n" +
                "                    )\n" +
                "                    \n" +
                "                group by rk.cubedesc_id\n" +
                "            ) u on a.cubedesc_id = u.cubedesc_id\n" +
                "            order by usageRate\n" +
                "            limit 10\n" +
                "        ) t\n" +
                "        join kylin_cube c on c.cubedesc_id = t.cubedesc_id";
        sql = sql.replaceAll("\\s+", " ");
        System.out.println(sql);
        String sqlBeautify =   org.hibernate.engine.jdbc.internal.FormatStyle.BASIC.getFormatter().format(sql);
        //sqlBeautify =   org.hibernate.engine.jdbc.internal.FormatStyle.BASIC.getFormatter().format(sqlBeautify);
        System.out.println(sqlBeautify);
    }
}
